import datetime
from typing import Any

from dbutils.pooled_db import PooledDB
from pymysql.cursors import DictCursor

from db_pool import DBDevice, SF_DB

"""
读取数据用
"""


class TaskData(DBDevice):
    # 主键
    SERIALKEY: str = None
    # 订单编号
    ORDERKEY: str = None
    # 订单创建时间
    ORDERDATE: str = None
    # 交付对象编码
    deliver_factory_code: str = None
    # 交付对象
    deliver_factory: str = None
    # 交付对象类型
    deliver_factory_type: int = None
    # 发车间隔
    deliver_interval: str | None = None
    # 交付截止时间
    deliver_config_end_time: str | None = None

    def to_dict(self):
        return {'SERIALKEY': self.SERIALKEY, 'ORDERKEY': self.ORDERKEY, 'ORDERDATE': self.ORDERDATE,
                'deliver_factory_code': self.deliver_factory_code, 'deliver_factory': self.deliver_factory,
                'deliver_factory_type': self.deliver_factory_type, 'deliver_interval': self.deliver_interval,
                'deliver_config_end_time': self.deliver_config_end_time, }


class CompletedCarTemplate(DBDevice):
    # 装车开始时间
    plan_begin: str = None
    # 装车结束时间
    plan_end: str = None
    # 件箱开始时间
    box_begin: datetime.datetime = None
    # 件箱结束时间
    box_end: datetime.datetime = None
    # 栈板开始时间
    pallet_begin: datetime.datetime = None
    # 栈板结束时间
    pallet_end: datetime.datetime = None
    # 装车任务ID
    wops_vehicle_taskid: str = None
    # 件箱占用出货口情况
    ems_case_deliver_port_num: str = None
    # 栈板占用出货口情况
    ems_pallet_deliver_port_num: str = None
    # 订单详情ID
    orderdetil: str = None
    # 装车件箱pcs数
    box_pcs_num: int = None
    # 装车栈板pcs数
    pallet_pcs_num: int = None
    # 装车件箱数
    box_num: int = None
    # 装车栈板数
    pallet_num: int = None
    # 交付对象
    deliver_factory_code: str = None
    # 订单类型
    CONSIGNEEKEY: str = None

    def to_dict(self):
        # 返回对象属性的字典副本
        return {key: value for key, value in self.__dict__.items()}


class GlobalTime(DBDevice):
    # ID
    id: str = None
    # 工作类型
    type: int = None
    # 开始时间
    begin_time: datetime.datetime = None
    # 结束时间
    end_time: datetime.datetime = None

    def to_dict(self):
        # 返回对象属性的字典副本
        return {key: value for key, value in self.__dict__.items()}


class TaskDataService(object):
    """
    获取所有任务基础数据数据
    """
    __pool: PooledDB = None

    def __init__(self):
        self.__pool = SF_DB().get_pool()

    def get_port_config_data(self) -> Any:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query('SELECT * FROM wops_delivery_port_config')
        return data_list

    def get_completed_loading(self):

        result_list: list[CompletedCarTemplate] = []
        max_seq_id = None
        max_seq_id_state = None
        insert_time = None
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)

            # Step 1: 获取今天的最大序列值
            max_seq_query = """
                SELECT MAX(seq) AS max_seq 
                FROM wops_wavelet_order 
                WHERE DATE(work_date) = DATE(NOW()) 
                AND (state = 2 OR state = 3)
            """
            cursor.execute(max_seq_query)
            max_seq_result = cursor.fetchone()
            max_seq = max_seq_result['max_seq']

            if max_seq is None:
                return result_list, insert_time, max_seq_id, max_seq_id_state

            # Step 2: 获取主数据列表
            main_query = """
                SELECT id, seq, plan_end, state
                FROM wops_wavelet_order 
                WHERE DATE(work_date) = DATE(NOW()) 
                AND seq <= %s + 1
            """
            cursor.execute(main_query, (max_seq,))
            main_data_list = cursor.fetchall()

            # Step 3: 获取与 wops_kwms_work_queue 相关的数据
            wavelet_ids = [data['id'] for data in main_data_list]
            target_data = next((row for row in main_data_list if row['seq'] == max_seq + 1), None)
            max_seq_data = next((row for row in main_data_list if row['seq'] == max_seq), None)
            if not wavelet_ids:
                return result_list, insert_time, max_seq_id, max_seq_id_state
            if target_data is not None:
                insert_time = target_data.get('plan_end')
            else:
                insert_time = max_seq_data.get('plan_end')

            if max_seq_data is not None:
                max_seq_id = max_seq_data.get('id')
                max_seq_id_state = max_seq_data.get('state')

            queue_query = """
                SELECT wops_vehicle_taskid , wops_wavelet_orderid
                FROM wops_kwms_work_queue 
                WHERE wops_wavelet_orderid IN %s 
                AND state != 5
            """
            cursor.execute(queue_query, (tuple(wavelet_ids),))
            queue_data_list = cursor.fetchall()

            # Step 4: 获取与 wops_vehicle_task 相关的数据
            vehicle_task_ids = {data['wops_vehicle_taskid'] for data in queue_data_list}
            if not vehicle_task_ids:
                return result_list, insert_time, max_seq_id, max_seq_id_state

            vehicle_task_query = """
                SELECT id, plan_begin, plan_end, ems_case_deliver_port_num, ems_pallet_deliver_port_num,
                       pallet_begin, pallet_end, box_begin, box_end
                FROM wops_vehicle_task 
                WHERE id IN %s
            """
            cursor.execute(vehicle_task_query, (tuple(vehicle_task_ids),))
            vehicle_task = cursor.fetchall()
            if not vehicle_task:
                return result_list, insert_time, max_seq_id, max_seq_id_state

            vehicle_task_data_list = {data['id']: data for data in vehicle_task}
            if not vehicle_task_data_list:
                return result_list, insert_time, max_seq_id, max_seq_id_state

            # Step 5: 获取与 wops_wave_order_sku 相关的数据
            sku_query = """
                SELECT box_pcs_num, pallet_pcs_num,
                       deliver_factory_code, box_num, pallet_num, wops_vehicle_taskid, orderdetil
                FROM wops_wave_order_sku 
                WHERE wops_vehicle_taskid IN %s
            """
            cursor.execute(sku_query, (tuple(vehicle_task_ids),))
            sku_data_list = {}
            sku_list = cursor.fetchall()
            if not sku_list:
                return result_list, insert_time, max_seq_id, max_seq_id_state
            for sku_data in sku_list:
                sku_data_list[sku_data['wops_vehicle_taskid']] = sku_data

            # Step 6: 获取与 orderdetail 相关的数据
            order_detail_serialkeys = [sku['orderdetil'] for sku in sku_data_list.values() if sku['orderdetil']]
            if not order_detail_serialkeys:
                return result_list, insert_time, max_seq_id, max_seq_id_state
            order_detail_query = """
                                SELECT ORDERKEY , SERIALKEY
                                FROM orderdetail 
                                WHERE SERIALKEY IN %s
                            """
            cursor.execute(order_detail_query, (tuple(order_detail_serialkeys),))
            order_detail_data = cursor.fetchall()
            if not order_detail_data:
                return result_list, insert_time, max_seq_id, max_seq_id_state
            order_detail_data_list = {data['SERIALKEY']: data for data in order_detail_data}

            # Step 7: 获取与 orders 相关的数据
            order_keys = [data['ORDERKEY'] for data in order_detail_data_list.values() if 'ORDERKEY' in data]
            if not order_keys:
                return result_list, insert_time, max_seq_id, max_seq_id_state
            order_query = """
                                SELECT ORDERKEY, CONSIGNEEKEY
                                FROM orders 
                                WHERE ORDERKEY IN %s
                            """
            cursor.execute(order_query, (tuple(order_keys),))
            order = cursor.fetchall()
            if not order:
                return result_list, insert_time, max_seq_id, max_seq_id_state
            order_data_list = {data['ORDERKEY']: data for data in order}

            # Step 8: 合并数据
            for main_data in queue_data_list:

                vehicle_task_id = main_data['wops_vehicle_taskid']
                vehicle_task_data = vehicle_task_data_list.get(vehicle_task_id, {})
                sku_data = sku_data_list.get(vehicle_task_id, {})
                order_detail_data = order_detail_data_list.get(sku_data.get('orderdetil'), {})
                order_data = order_data_list.get(order_detail_data.get('ORDERKEY'), {})

                # 合并所有数据
                if vehicle_task_data and sku_data and order_data:
                    main_data.update(vehicle_task_data)
                    main_data.update(sku_data)
                    main_data['CONSIGNEEKEY'] = order_data.get('CONSIGNEEKEY')
                    t = CompletedCarTemplate(**main_data)
                    result_list.append(t)

            # 返回最终结果
            return result_list, insert_time, max_seq_id, max_seq_id_state

    def get_insert_time(self) -> datetime.datetime | None:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            cursor.execute("SELECT plan_end FROM wops_wavelet_order o1 "
                           "JOIN (  "
                           "SELECT MAX(seq) AS max_seq FROM wops_wavelet_order WHERE DATE( work_date ) = DATE( NOW()) AND (state = 2 OR state = 3) "
                           ") o2 ON o1.seq = o2.max_seq + 1 "
                           "WHERE DATE( o1.work_date ) = DATE(NOW()) ")
            data = cursor.fetchone()
            if data is None:
                return None
        return data.get("plan_end")

    def get_dis_data(self) -> Any:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            cursor.execute("SELECT value FROM wops_parameter_config_dtl WHERE code = 'redfactor'")
            data = cursor.fetchone()
            return data

    def get_box_flow(self) -> Any:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            cursor.execute("SELECT value FROM wops_parameter_config_dtl WHERE code = 'box_flow'")
            data = cursor.fetchone()
            return data

    def get_time_window_execution_time(self) -> Any:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            cursor.execute("SELECT value FROM wops_parameter_config_dtl WHERE code = 'time_window_execution_time'")
            data = cursor.fetchone()
            return data

    def get_config_data(self) -> Any:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data = cursor.db_query("SELECT code,value FROM wops_parameter_config_dtl")
            return data

    def get_global_time(self) -> list[GlobalTime]:
        with self.__pool.get_connection() as conn:
            result_list: list[GlobalTime] = []
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query("SELECT * FROM wops_work_calendar "
                                        "WHERE DATE(work_date) BETWEEN DATE(NOW())"
                                        "AND DATE_ADD(NOW(), INTERVAL 1 DAY )"
                                        "AND type = 1")
            for d in data_list:
                t = GlobalTime(**d)
                result_list.append(t)

            return result_list

    def get_port_lpn_num(self) -> Any:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query('SELECT value FROM wops_parameter_config_dtl WHERE code '
                                        'in ("3portEmsLpnNum","2portEmsLpnNum","2portSelfLpnNum")')
            lpn_num = []
            for data in data_list:
                lpn_num.append(data.get('value'))

        return lpn_num

    def get_port_assignment_data(self) -> Any:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query('SELECT code,value FROM wops_parameter_config_dtl WHERE'
                                        ' parameter_configgroup_code = "export_allocation" ')
        return data_list
